SQL Server 2008 brought the temporal offerings up to ANSI/ISO Standards. It is my feeling that the new temporal data types will displace the “Sybase/UNIX Code Museum that still dominates T-SQL code. We now have DATE and TIME data types with a full range and high precision.

To re-cap, for decades T-SQL has had the DATETIME data type. It is a crippled eight bytes implementation of what ANSI would call a TIMESTAMP. It combines both a clock time and date with a range from 1753-01-01 to 9999-12-31 with an accuracy of 3.33 milliseconds. That start date has to do with the history of the Gregorian Calendar. I will not get into that, but you might want to Google it when you have a little spare time. The 3.33 millisecond increment has to do with the early 16-bit hardware and the system clocks in those days.

T-SQL has an interesting proprietary temporal data type called SMALLDATETIME. It uses four bytes to hold time and date values ranging from 1900-01-01 to 2079-06-06 with an accuracy of one minute. For most commercial purposes, this range is just fine; very few companies track anything down to the second. Strangely, it is not used as much as DATETIME.

Now, about the reserved word TIMESTAMP. In T-SQL dialect it was an internal increasing numbering that that was used for version control and optimistic concurrency control. It is a table property and not a data type at all. That means each table may have only one TIMESTAMP and they cannot be NULL.

The good news is the T-SQL TIMESTAMP is deprecated. That it will be removed in a future version of T-SQL. This is one of the reasons I tell people to avoid dialect in favor of Standard SQL. In fact, now is the time to start cleaning up your old code before you get caught in a crunch.

Since the T-SQL TIMESTAMP is a table property, you do not have to specify a column name for it. You will get a horrible system generated name instead.

The replacement for the old T-SQL TIMESTAMP is called ROWVERSION. It is almost a synonym for the old T-SQL TIMESTAMP, but must you specify a column name, for example:

The bad news is that you can get duplicate ROWVERSION values by using the SELECT INTO statement in which a ROWVERSION column is in the SELECT list. This is not recommended, even tho it follows a set-oriented model of insertion. Since these things are meta-data and not temporal, this is all I am saying about them.

We now have a DATETIME2(n) data type. Microsoft stole this postfix digit 2 from Oracle and their VARCHAR2() data type. A DATETIME2(n) uses between six to eight bytes to store dates and times as a single unit (the ANSI/ISO TIMESTAMP). The date range is between 0001-01-01 and 9999-12-31 with an accuracy of one hundred nanoseconds (seven decimal places). The FIPS-127 (Federal Information Processing Standards) specs require at least five decimal places for seconds. The nice part is that you can adjust the decimal places in the declaration. A zero will give you whole seconds, and seven will give you maximum precision.

DATE Data Type

The DATE data type use three bytes to store a date only (between 0001-01-01 to 9999-12-31. This is one of the nicest things we have gotten in T-SQL in a long time. When T-SQL only had DATETIME, we spent lots of computing time trimming the time fields to ’00:00:00′ for comparisons and grouping. This also messed up indexing by putting temporal columns in functions calls.

[Before anyone jumps on me, the ANSI/ISO Standard uses the term fields for the parts of a temporal data types.]

The rounding error in DATETIME also made it hard to use the BETWEEN predicate with temporal ranges. We had to write things like this to ensure we got all of fractional seconds in New Year’s Day.

now we can use this for a single day

or get a range with.

Notice how natural and simple the CAST( <exp> AS DATE) is to use. The worst way to handle dates was to use the proprietary CONVERT() function, turn the temporal data into a string, edit the string and then cast it back to temporal data. The ANSI/ISO Standards use only one data display format; the ISO-8601 “yyyy-mm-dd” with dashes. This is also what the DATE data type defaults to. This standard also shows up in many other ISO Standards, so it would be a good idea to get rid of your old local dialect dates.

The proprietary DATEADD() function will work with DATE values, but the units have to be YEAR, MONTH or DAY. No, it will not convert 24 hours into a day for you.

TIME Data Types

The TIME data types use between three to five bytes to store a time of day to an accuracy of 100 nanoseconds. Now, we have some language problems when we talk about time. We can talk about a point in time or in a day (“Meet me at 15:00 Hrs.”), a duration (“The concert lasted 2 hours.”) or an interval (“The movie starts at 15:00 and ends at 17:30 Hrs”).

The ISO model of time is a continuum, so you have to use half-open intervals. That means a day starts exactly at 00:00:00 Hrs (midnight) and ends at 23:59:59.999.. Hrs without every getting to the start of the next day. This means that 24:00:00 Hrs today is really 00:00:00 Hrs tomorrow. DB2 will accept this convention and increment the date in a Standard timstamp value; T-SQL will throw an exception.

The proprietary DATEADD() function will work with TIME values, but the units have to be SECOND, MINUTE or HOUR. No, it will not convert a day into 24 hours for you. But the time of day will cycle around if you exceed 24 hours:

The handiest trick I have found so far for TIME is to build a reporting range table of time slots in day. For example, if you wanted to set up time slots of one minute, you only need (60 minutes per hour * 24 hours per day) = 1440 rows. That is small enough to fit into main storage.

The data being put into the buckets is TIME(0)and the “edges of the buckets” are to a tenth of a second, so we can use:

Obviously, I can do any of the other aggregate functions on the time slots, with AVG() and SUM() being the most likely.

There is but one Standard time on Earth and it is UTC (Universal Coordinated Time) it is now defined by an atomic clock. The first thing people ask is why the three letter abbreviation does not match the name of the Standard. The answer is: The French. There is also the UT1 or astronomical time Astronomical or mean solar time,. This is based on the rotation of Earth, which is irregular.

The atomic second was originally defined by comparing atomic clocks to the Ephemeris second, which is slightly shorter than the mean solar second. This made the atomic second slightly shorter than the mean solar second. Now throw in the wobble in the Earth’s rotation and you get a difference between UTC and UT1.

The International Earth Rotation and Reference System Service (IERS) observes the Earth’s rotation and issues a bulletin in January and July about a correction factor to align UTC and UT1 within 0.9 seconds. The correction is called the “leap second” and it is added or subtracted at the end of June and December of the bulletin’s year. If you have a clock that uses the NIST (National Institute for Science and Technology) time signal, you can watch the seconds display field either stay “:59” or advance to “:60” when leap second applies.

No positive leap second was added at the end of 2010. That may also have been the last year that we had the leap second. There is also a strong move to do away with leap seconds completely. UT1 is only used by astronomers and people with very accurate sundials. UTC is now built into computers and electronics. No positive leap second was introduced at the end of December 2010. One proposal is to replace it with a leap hour approximately every 600 years. ITU’s Radio Communication Sector (ITU-R), which oversees UTC, will draft a proposal to drop leap seconds in 2012 to be implemented by 2018.

While you might be thinking leap seconds are an obscure bit of geek trivia, ask an Oracle programmers about his clusters re-setting in 2008 when a leap second hit.

T-SQL now has a GETUTCDATE() function the returns the UTC timestamp. The old GETDATE() and the ANSI/ISO compliant version CURRENT_TIMESTAMP return the local server’s clock setting. This was a problem for companies that operated over multiple time zones. You had to write your own code to handle this and hope you got it right. This leads us to the next topic.

DATETIMEOFFSET Data Type

The DATETIMEOFFSET data type is another ANSI/ISO Standard. It is a Standard SQL timestamp with the time zone displacement in hours and minutes on the right hand end of the string. These values use between eight and ten bytes.

or

There are no spaces in the string. The letter ‘T’ is a separator and it is not optional. Likewise, we need the plus or minus sign. The letter ‘Z’ is short for ‘Zulu’, which was an older name for Greenwich Time (GMT) and now for UTC.

These two formats are not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. Obviously, the server clock has to be considered.

Daylight Saving Time

While we did get time zones with the new DATETIMEOFFSET data type, you are still on your own for Daylight Saving Time (DST). There is a good map of where and when DST is observed anywhere on Earth at http://www.worldtimezone.com/daylight.html.

DST is also observed in about 70 countries. Other parts of the world observe Daylight Saving Time as well. In 1996 the European Union (EU) standardized an EU-wide “summertime period” that starts on the last Sunday in March through the last Sunday in October, with the switch over at 01:00 Hrs.

Russia is on a different system, Equatorial and tropical countries generally do not bother and the Southern Hemisphere has reversed seasons. In short, you need to Google this stuff and find what you need to know for each locale.

In the US, NIST Time Services sets the rules for DST calculations, but does not have the power to enforce them. That job belongs to the US Department of Transportation. What is called “lawful time” is local in the United States. DST is not observed in Hawaii, American Samoa, Guam, Puerto Rico, the Virgin Islands, most of the Eastern Time Zone portion of Indiana and Arizona. However, the Navajo Indian Reservation, which goes over three states including Arizona, is on DST.

The goal of DST is to get more sunlit hours in the evening during months when the weather is the warmest. The memory aid is “spring forward, fall back” is handy, but it does not give us dates and times.

Daylight Saving Time rules changed in 2007 for the first time in over 20 years. This was a bit of a mess for a lot of software. The new rules were part of the Energy Policy Act of 2005, which extended the length of DST by a month to reduce energy consumption (it saved about 1% of the electricity in California).

Daylight Saving Time in the United States begins at 02:00 Hrs. on the second Sunday of March and ends at 02:00 Hrs on the first Sunday of November. In 2010, DST was from 02:00 Hrs (local time) on 2010-03-14 until 02:00 Hrs (local time) on 2010-11-07. In 2011, DST is from 02:00 Hrs (local time) on 2011-03-13 until 02:00 Hrs (local time) on 2011-11-06.

Do not waste time writing a Boolean function to compute whether or not a date is in DST. You should have a Calendar table where you can keep 50 to 100 years of this kind of data. Use a VIEW to join to the Calendar.